<?php
include_once 'dbconnect.php';
$sql = new ownedsql;

class officeManager
{
    function overview()
    {
        global $sql;
        if (isAdmin() == true) {


            $sql->connect();
            $sql->query("SELECT *,(SELECT group_concat(dName separator ', ') FROM 3i_bp_district WHERE 3i_offices.oid=oid) as officeArea FROM 3i_offices LEFT JOIN 3i_userdetails ON 3i_userdetails.uid=3i_offices.oRuler");
            $result = $sql->query;
            $sql->kill();

            if (mysql_num_rows($result) == 0) {
?>
            <div id="noResult">Nincs iroda az adatbázisban</div>
            <?php
            } else {
                while ($item = mysql_fetch_array($result)) {
?>
            <table id="overViewTable">
                <tr>
                    <td id="office"><?php echo "<span>" . $item['oName'] .
                    "</span> - iv.:" . $item['sName'] . " " . $item['fName'] ?></td>
                    <td></td>
                </tr>
                <tr>
                    <td id="districts">Kerületek: <?php echo $item['officeArea'] ?></td>
                    <td id="action"><a href="office.php?mode=edit&oid=<?php echo
                        $item['oid'] ?>">Szerkesztés</a> | <a href="office.php?mode=assign&oid=<?php echo
                        $item['oid'] ?>">Hozzárendelés</a> | Törlés</td>
                </tr>
            </table>
                <?php
                }

            }

        } else {
?>
            <div id="noAccess">Hozzáférés megtagadva!</div>
            <?php
        }

    }

    function newOfficeForm()
    {
        global $sql;

        if (isAdmin() == true) {
            $sql->connect();
?>
            <form method="post" action="">
                <fieldset>
                    <legend>Új Iroda</legend>
                    <table>
                        <tr>
                            <td>Iroda neve:</td>
                            <td><input type="text" tabindex="1" name="oname" /></td>
                        </tr>
                        <tr>
                            <td>Irányító szám:</td>
                            <td><input type="text" tabindex="2" name="ozip" /></td>
                        </tr>
                        <tr>
                            <td>Város:</td>
                            <td>
                                <select name="ocity" tabindex="3">
                                <?php
            $sql->query("SELECT * FROM 3i_cities ORDER BY cityName");
            $result = $sql->query;

            while ($item = mysql_fetch_array($result)) {
                echo "<option value=\"" . $item['cityid'] . "\">" . $item['cityName'] .
                    "</option>\n";
            }
?>                                    
                                </select>
                            </td>
                        </tr>
                        <tr>
                            <td>út, utca:</td>
                            <td><input type="text" name="oaddr" tabindex="4" /></td>
                        </tr>
                        <tr>
                            <td>Tel.:</td>
                            <td><input type="text" name="otel" tabindex="5" /></td>
                        </tr>
                        <tr>
                            <td>em@il:</td>
                            <td><input type="text" name="oemail" tabindex="6" /></td>
                        </tr>
                        <tr>
                            <td>Iroda vezető:</td>
                            <td>
                                <select name="oruler" tabindex="7">
                                <?php

            $sql->query("SELECT * FROM 3i_userdetails ORDER BY sName ASC");
            $result = $sql->query;

            while ($item = mysql_fetch_array($result)) {
                echo "<option value=\"" . $item['uid'] . "\">" . $item['sName'] . " " . $item['fName'] .
                    "</option>\n";
            }

?>
                                </select>
                            </td>
                        </tr>
                        <tr>
                            <td colspan="2" ><input type="submit" name="submit" value="Mentés" /></td>
                        </tr>
                    </table>
                </fieldset>
            </form>
           <?php
            $sql->kill();

        } else {
?>
            <div id="noAccess">Hozzáférés megtagadva!</div>
            <?php
        }


    }

    function clean_var($variable)
    {
        $variable = strip_tags(stripslashes(trim(rtrim(mysql_real_escape_string($variable)))));
        return $variable;
    }

    function validEmail($email)
    {
        $isValid = true;
        $atIndex = strrpos($email, "@");
        if (is_bool($atIndex) && !$atIndex) {
            $isValid = false;
        } else {
            $domain = substr($email, $atIndex + 1);
            $local = substr($email, 0, $atIndex);
            $localLen = strlen($local);
            $domainLen = strlen($domain);
            if ($localLen < 1 || $localLen > 64) {
                // local part length exceeded
                $isValid = false;
            } else
                if ($domainLen < 1 || $domainLen > 255) {
                    // domain part length exceeded
                    $isValid = false;
                } else
                    if ($local[0] == '.' || $local[$localLen - 1] == '.') {
                        // local part starts or ends with '.'
                        $isValid = false;
                    } else
                        if (preg_match('/\\.\\./', $local)) {
                            // local part has two consecutive dots
                            $isValid = false;
                        } else
                            if (!preg_match('/^[A-Za-z0-9\\-\\.]+$/', $domain)) {
                                // character not valid in domain part
                                $isValid = false;
                            } else
                                if (preg_match('/\\.\\./', $domain)) {
                                    // domain part has two consecutive dots
                                    $isValid = false;
                                } else
                                    if (!preg_match('/^(\\\\.|[A-Za-z0-9!#%&`_=\\/$\'*+?^{}|~.-])+$/', str_replace("\\\\",
                                        "", $local))) {
                                        // character not valid in local part unless
                                        // local part is quoted
                                        if (!preg_match('/^"(\\\\"|[^"])+"$/', str_replace("\\\\", "", $local))) {
                                            $isValid = false;
                                        }
                                    }
            if ($isValid && function_exists('checkdnsrr')) {
                if (!(checkdnsrr($domain, "MX") || checkdnsrr($domain, "A"))) {
                    // domain not found in DNS
                    $isValid = false;
                }
            }
        }
        return $isValid;
    }

    function newOfficeAction()
    {
        global $_POST, $sql;

        if (isAdmin() == true) {
            $oname = $this->clean_var($_POST['oname']);
            $ozip = $this->clean_var($_POST['ozip']);
            $ocity = $this->clean_var($_POST['ocity']);
            $oaddr = $this->clean_var($_POST['oaddr']);
            $otel = $this->clean_var($_POST['otel']);
            $oemail = $this->clean_var($_POST['oemail']);
            $oruler = $this->clean_var($_POST['oruler']);

            $reason = "";

            //email hitelesítés
            if ($this->validEmail($oemail) == true) {
                $proceed = true;
            } else {
                $proceed = false;
                $reason .= "<p id=\"formError\">Érvénytelen email cím</p>";
            }

            //irányítószám hitelesítés
            //a string 4 karakterböl áll -e?
            if (strlen($ozip) == 4) {
                $proceed = true;

                //a 4 karakter szám -e?
                if (preg_match('(\d{4})', $ozip)) {
                    $proceed = true;
                } else {
                    $proceed = false;
                    $reason .= "<p id=\"formError\">Nem megfelelő irányítószám</p>";
                }
            } else {
                $proceed = false;
                $reason .= "<p id=\"formError\">Nem megfelelő irányítószám</p>";
            }

            //telefonszám ellenőrzése
            if (preg_match('/(\+36){1}[\-](1|20|30|70){1}[\-](\d{3}){1}[\-](\d{4}){1}/', $otel)) {
                $proceed = true;
            } else {
                $proceed = false;
                $reason .= "<p id=\"formError\">Nem megfelelő telefonszám: +36-1/20/30/70-XXX-XXXX</p>";
            }

            if ($proceed == true) {
                $sql->connect();
                $sql->query("INSERT 3i_offices (oName, oZip, oCity, oAdrr, oRuler, oTel, oEmail) VALUES ('$oname', '$ozip', '$ocity', '$oaddr', '$oruler', '$otel', '$oemail')");
                $sql->kill();
                $this->overview();
            } else {
                echo $reason;
                $this->newOfficeForm();
            }

        } else {
?>
            <div id="noAccess">Hozzáférés megtagadva!</div>
            <?php
        }
    }

    function editOfficeForm($oid)
    {
        global $sql;

        if (isAdmin() == true) {
            $sql->connect();
            $sql->query("SELECT * FROM 3i_offices WHERE oid='$oid'");
            $result = $sql->query;
            while ($form = mysql_fetch_array($result)) {
?>
            <form method="post" action="">
                <fieldset>
                    <legend>Iroda szerkesztése</legend>
                    <table>
                        <tr>
                            <td>Iroda neve:</td>
                            <td><input type="text" tabindex="1" name="oname" value="<?php echo
                $form['oName'] ?>" /></td>
                        </tr>
                        <tr>
                            <td>Irányító szám:</td>
                            <td><input type="text" tabindex="2" name="ozip" value="<?php echo
                    $form['oZip'] ?>" /></td>
                        </tr>
                        <tr>
                            <td>Város:</td>
                            <td>
                                <select name="ocity" tabindex="3">
                                <?php
                $sql->query("SELECT * FROM 3i_cities ORDER BY cityName");
                $result2 = $sql->query;

                while ($item = mysql_fetch_array($result2)) {
                    if ($item['cityid'] == $form['oCity']) {
                        $select1 = "selected=\"selected\"";
                    } else {
                        $select1 = "";
                    }
                    echo "<option value=\"" . $item['cityid'] . "\" " . $select1 . ">" . $item['cityName'] .
                        "</option>\n";
                }
?>                                    
                                </select>
                            </td>
                        </tr>
                        <tr>
                            <td>út, utca:</td>
                            <td><input type="text" name="oaddr" tabindex="4" value="<?php echo
                $form['oAdrr'] ?>" /></td>
                        </tr>
                        <tr>
                            <td>Tel.:</td>
                            <td><input type="text" name="otel" tabindex="5" value="<?php echo
                    $form['oTel'] ?>" /></td>
                        </tr>
                        <tr>
                            <td>em@il:</td>
                            <td><input type="text" name="oemail" tabindex="6" value="<?php echo
                    $form['oEmail'] ?>" /></td>
                        </tr>
                        <tr>
                            <td>Iroda vezető:</td>
                            <td>
                                <select name="oruler" tabindex="7">
                                <?php

                $sql->query("SELECT * FROM 3i_userdetails ORDER BY sName ASC");
                $result2 = $sql->query;

                while ($item = mysql_fetch_array($result2)) {
                    if ($item['uid'] == $form['oRuler']) {
                        $select2 = "selected=\"selected\"";
                    } else {
                        $select2 = "";
                    }
                    echo "<option value=\"" . $item['uid'] . "\" " . $select2 . ">" . $item['sName'] .
                        " " . $item['fName'] . "</option>\n";
                }

?>
                                </select>
                            </td>
                        </tr>
                        <tr>
                            <td colspan="2" >
                                <input type="hidden" name="oid" value="<?php echo
$oid ?>" />
                                <input type="submit" name="submit" value="Mentés" />
                            </td>
                        </tr>
                    </table>
                </fieldset>
            </form>
           <?php
            }

            $sql->kill();

        } else {
?>
            <div id="noAccess">Hozzáférés megtagadva!</div>
            <?php
        }
    }

    function editOfficeAction()
    {
        global $_POST, $sql;

        if (isAdmin() == true) {
            $oname = $this->clean_var($_POST['oname']);
            $ozip = $this->clean_var($_POST['ozip']);
            $ocity = $this->clean_var($_POST['ocity']);
            $oaddr = $this->clean_var($_POST['oaddr']);
            $otel = $this->clean_var($_POST['otel']);
            $oemail = $this->clean_var($_POST['oemail']);
            $oruler = $this->clean_var($_POST['oruler']);
            $oid = $this->clean_var($_POST['oid']);

            $reason = "";

            //email hitelesítés
            if ($this->validEmail($oemail) == true) {
                $proceed = true;
            } else {
                $proceed = false;
                $reason .= "<p id=\"formError\">Érvénytelen email cím</p>";
            }

            //irányítószám hitelesítés
            //a string 4 karakterböl áll -e?
            if (strlen($ozip) == 4) {
                $proceed = true;

                //a 4 karakter szám -e?
                if (preg_match('(\d{4})', $ozip)) {
                    $proceed = true;
                } else {
                    $proceed = false;
                    $reason .= "<p id=\"formError\">Nem megfelelő irányítószám</p>";
                }
            } else {
                $proceed = false;
                $reason .= "<p id=\"formError\">Nem megfelelő irányítószám</p>";
            }

            //telefonszám ellenőrzése
            if (preg_match('/(\+36){1}[\-](1|20|30|70){1}[\-](\d{3}){1}[\-](\d{4}){1}/', $otel)) {
                $proceed = true;
            } else {
                $proceed = false;
                $reason .= "<p id=\"formError\">Nem megfelelő telefonszám: +36-1/20/30/70-XXX-XXXX</p>";
            }

            if ($proceed == true) {
                $sql->connect();
                $sql->query("UPDATE 3i_offices SET oName='$oname', oZip='$ozip', oCity='$ocity', oAdrr='$oaddr', oRuler='$oruler', oTel='$otel', oEmail='$oemail' WHERE oid='$oid'");
                $sql->kill();
                $this->overview();
            } else {
                echo $reason;
                $this->newOfficeForm();
            }

        } else {
?>
            <div id="noAccess">Hozzáférés megtagadva!</div>
            <?php
        }

    }
    function assignOfficeDistrictForm($oid)
    {
        global $sql;

        $sql->connect();
        $sql->query("SELECT * FROM 3i_bp_district WHERE oid='0' OR oid='$oid'");
        $result = $sql->query;
        $sql->kill();

?>
        <form method="post" action="">
        <fieldset>
            <legend>Kerület hozzárendelés</legend>
            <table>
                <tr>
                    <td>
                    <?php
        while ($item = mysql_fetch_array($result)) {
?>
                        <div id="selBox"><?php echo $item['dName'] . " "; ?><input type="checkbox" value="<?php echo
$item['did'] ?>" name="distr[]" <?php if ($item['oid'] ==
$oid) { ?> checked="checked"  <?php } ?> /></div>
                        <?php
        }
?>
                    </td>
                </tr>
                <td>
                    <td>
                        <input type="hidden" value="<?php echo $oid ?>" name="oid" />
                        <input type="submit" name="assignSubmit" value="Mentés" />
                    </td>
                </td>
            </table>
        </fieldset>
        </form>
        <?php
    }

    function assignOfficeDistrictAction()
    {
        global $sql, $_POST;

        $oid = $_POST['oid'];
        $district = $_POST['distr'];

        $sql->connect();

        $query = "UPDATE 3i_bp_district SET oid='0' WHERE oid='$oid'";
        $sql->query($query);

        foreach ($district as $val) {
            $query = "UPDATE 3i_bp_district SET oid = '$oid' WHERE did='$val'";
            // echo $query . "<br />";
            $sql->query($query);
        }
        $sql->kill();

        echo "<div id=\"success\">Sikeres frissítés!</div>";
        
        $this->overview();
    }
    function deleteOfficeAction() {
        //TODO: törlés megcsinálása, adatbázis relációk default helyzetbe hozása
    }
}
?>